package com.scholarship.system.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.scholarship.system.entity.StudentRanking;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 排名Mapper接口
 */
@Mapper
public interface RankingMapper extends BaseMapper<StudentRanking> {

    /**
     * 查询学生排名列表
     *
     * @param college  学院
     * @param major    专业
     * @param grade    年级
     * @param keyword  关键字
     * @param year     年份
     * @param semester 学期
     * @return 排名列表
     */
    @Select("<script>" +
            "SELECT sr.*, s.student_id as student_no, u.name " +
            "FROM student_ranking sr " +
            "JOIN student s ON sr.student_id = s.id " +
            "JOIN user u ON s.user_id = u.id " +
            "WHERE 1=1 " +
            "<if test='college != null and college != \"\"'> AND sr.college = #{college} </if>" +
            "<if test='major != null and major != \"\"'> AND sr.major = #{major} </if>" +
            "<if test='grade != null and grade != \"\"'> AND sr.grade = #{grade} </if>" +
            "<if test='keyword != null and keyword != \"\"'> " +
            "   AND (s.student_id LIKE CONCAT('%', #{keyword}, '%') OR u.name LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "<if test='year != null'> AND sr.year = #{year} </if>" +
            "<if test='semester != null and semester != \"\"'> AND sr.semester = #{semester} </if>" +
            "ORDER BY sr.rank ASC" +
            "</script>")
    List<Map<String, Object>> selectRankingList(
            @Param("college") String college,
            @Param("major") String major,
            @Param("grade") String grade,
            @Param("keyword") String keyword,
            @Param("year") Integer year,
            @Param("semester") String semester);

    /**
     * 更新学生排名
     *
     * @param year     年份
     * @param semester 学期
     * @return 影响行数
     */
    @Update("UPDATE student_ranking sr " +
            "JOIN (" +
            "   SELECT id, ROW_NUMBER() OVER (ORDER BY total_score DESC) as new_rank " +
            "   FROM student_ranking " +
            "   WHERE year = #{year} AND semester = #{semester}" +
            ") ranks ON sr.id = ranks.id " +
            "SET sr.rank = ranks.new_rank " +
            "WHERE sr.year = #{year} AND sr.semester = #{semester}")
    int updateRanking(@Param("year") Integer year, @Param("semester") String semester);

    /**
     * 获取学院列表
     *
     * @return 学院列表
     */
    @Select("SELECT DISTINCT college FROM student ORDER BY college")
    List<String> selectCollegeList();

    /**
     * 根据学院获取专业列表
     *
     * @param college 学院
     * @return 专业列表
     */
    @Select("SELECT DISTINCT major FROM student WHERE college = #{college} ORDER BY major")
    List<String> selectMajorListByCollege(@Param("college") String college);

    /**
     * 根据专业获取年级列表
     *
     * @param major 专业
     * @return 年级列表
     */
    @Select("SELECT DISTINCT grade FROM student WHERE major = #{major} ORDER BY grade")
    List<String> selectGradeListByMajor(@Param("major") String major);
}